﻿using BySoft.Server.Common;
using BySoft.Server.Common.Entity;
using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess
{
    public partial class BsOutrepairDA
    {
        //主档，明细档，材料，供应商，工序
        public static DataTable SelectList(string strBillID, string strWorkOrderNo, string strSupplierID, string strFrom, string strEnd, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"select  a.*,boo.OutProcessID,boo.BackProcessID,mp.ProcessName
 
   ,s.SupplierName,
  b.UnqualifyNum,
 -- b.MaterialID,
m.MaterialName,m.Specifications,m.Unit,
   mt.MaterialTypeName, mmst.MaterialSmallTypeName,
  -1 as IsNew,-1 as IsDel
from Bs_Outrepair a
LEFT JOIN BS_OutBackMat AS b  ON a.OutBackMatID=b.BillID

LEFT JOIN BS_OutOrder AS boo ON b.OutOrderID=boo.BillID
LEFT JOIN md_supplier s  on boo.SupplierID =s.SupplierID
LEFT JOIN MD_Process AS mp ON boo.OutProcessID=mp.ProcessID

LEFT JOIN md_material m ON b.MaterialID=m.MaterialID
LEFT JOIN md_materialType mt ON m.MaterialTypeID=mt.MaterialTypeID
LEFT  JOIN MD_MaterialSmallType AS mmst ON mmst.MaterialSmallTypeID = m.MaterialSmallTypeID
  WHERE a.[Status]='1' ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.WorkOrderNo like @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strSupplierID))
                {
                    strcmd += " and b.SupplierID = @SupplierID";
                    dpc.AddWithValue("@SupplierID", strSupplierID);
                }
                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and a.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strEnd))
                {
                    strcmd += " and a.BillDate <= @strEnd";
                    dpc.AddWithValue("@strEnd", strEnd);
                }
                strcmd += " order by a.BillDate desc";
                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 1.查询OutBackMat need repair明细
        ///  for Query select  back mat ID
        /// </summary>
        /// <param name="strBillID">本单号码，新增时为空</param>
        /// <param name="strProcessID">工序</param>
        /// <returns></returns>
        public static DataTable SelectOutNeedRepairDetail(string strCurrentReturnBillID, string strBillID,
            string strWorkOrderNo, string strSupplierID, string strProcessID,
            int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd;

                strcmd = @"---可返修数量

SELECT a.*,
    a.UnqualifyNum AS BackNums,
       mp.ProcessName                   AS OutProcessName,
      
       sply.SupplierName,
       m.MaterialName,
       m.Specifications,
       m.Unit,
       mt.MaterialTypeName,
       mmst.MaterialSmallTypeName,
       bo.OutProcessID,--bo.BackProcessID,
       bo.ScheduleID,bo.ScheduleDetailID,
       a.MaterialID AS BackMaterialID,
       a.UnqualifyNum -ISNULL(ret.returnNums, 0) AS CanBackNum,	--未回厂数量 
       -1                               AS IsNew,
       -1                               AS IsDel
from Bs_OutBackMat a
LEFT  JOIN BS_OutOrder AS bo ON a.OutOrderID=bo.billID
left join md_process mp on bo.OutProcessID=mp.ProcessID
LEFT JOIN md_material m ON bo.BackMaterialID=m.MaterialID
LEFT JOIN md_materialType mt ON m.MaterialTypeID=mt.MaterialTypeID
LEFT  JOIN MD_MaterialSmallType AS mmst ON mmst.MaterialSmallTypeID = m.MaterialSmallTypeID
Left join md_supplier sply  on a.supplierID=sply.supplierID
LEFT JOIN  (
        
        ---已返修数量
          SELECT ar.OutBackMatID,sum(Nums) as returnNums 
            from Bs_OutRepair aR 
             where aR.BillID<>@CurrentBillID
             group by  OutBackMatID
             )  ret on ret.OutBackMatID=a.BillID 
WHERE  a.UnqualifyNum -ISNULL(ret.returnNums, 0)>0
      

";



                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@CurrentBillID", strCurrentReturnBillID);//排除本单以外的数量

                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and a.BillID = @BillID";
                    dpc.AddWithValue("@BillID", strBillID);
                }
                if (!string.IsNullOrEmpty(strSupplierID))
                {
                    strcmd += " and a.SupplierID = @strSupplierID";
                    dpc.AddWithValue("@strSupplierID", strSupplierID);
                }
                if (!string.IsNullOrEmpty(strProcessID))
                {
                    strcmd += " and a.OutProcessID = @strProcessID";
                    dpc.AddWithValue("@strProcessID", strProcessID);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.WorkOrderNo like  @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo + "%");
                }
                strcmd += " order by BillID ";

                DataTable dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 还可返修材料数量
        /// </summary>
        /// <param name="strCurrentReturnBillID"></param>
        /// <param name="sendID"></param>
        /// <param name="materialID"></param>
        /// <returns></returns>
        public static DataTable SelectMatNeedRepairNum(string strCurrentReturnBillID, string sendID, string materialID)
        {

            try
            {

                //未填过
                string strcmd = @"SELECT a.MaterialID,a.BillID,
       a.UnqualifyNum as BackNums,
      
       a.UnqualifyNum -ISNULL(ret.returnNums, 0) AS CanBackNum --可返修数量 
      
from Bs_OutBackMat a

LEFT JOIN  (
           ---已返修数量
          SELECT ar.OutBackMatID,sum(Nums) as returnNums 
             from Bs_OutRepair aR 
             where aR.BillID<>@CurrentBillID
             group by  OutBackMatID
             )  ret on ret.OutBackMatID=a.BillID 
WHERE a.UnqualifyNum>0 AND  a.UnqualifyNum -ISNULL(ret.returnNums, 0)>0
    

";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@CurrentBillID", strCurrentReturnBillID);//排除本单以外的数量

                if (!string.IsNullOrEmpty(sendID))
                {
                    strcmd += " and a.BillID = @BillID";
                    dpc.AddWithValue("@BillID", sendID);
                }

                if (!string.IsNullOrEmpty(materialID))
                {
                    strcmd += " and a.MaterialID = @MaterialID";
                    dpc.AddWithValue("@MaterialID", materialID);
                }

                strcmd += " order by BillID ";

                DataTable dt = DataAccessCommon.Select(strcmd, dpc).Tables[0];


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
       




        #region View Data
        /// <summary>
        /// Header
        /// </summary>
        /// <param name="strBillID"></param>
        /// <returns></returns>
        public static DataTable SelectHeadData(string strBillID)
        {
            try
            {
                string strcmd = @"select  a.*,mp.ProcessName
   ,
   boo.ScheduleID,boo.ScheduleDetailID,
    s.SupplierName,boo.BillID,boo.BillDate,boo.ScheduleID,
      m.MaterialName,
       m.Specifications,
       m.Unit,
      b.UnqualifyNum as CanBackNum,
       boo.OutProcessID,boo.BackProcessID,
       boo.ScheduleID,boo.ScheduleDetailID,
       b.MaterialID ,b.SupplierID,
       
  -1 as IsNew,-1 as IsDel
from Bs_Outrepair a
LEFT JOIN BS_OutBackMat AS b  ON a.OutBackMatID=b.BillID
LEFT JOIN BS_OutOrder AS boo ON b.OutOrderID=boo.BillID
LEFT JOIN md_supplier s  on boo.SupplierID =s.SupplierID
LEFT JOIN MD_Process AS mp ON boo.OutProcessID=mp.ProcessID
LEFT JOIN md_material m ON b.MaterialID=m.MaterialID
    
where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// Detail
        /// </summary>
        /// <param name="strBillID"></param>
        /// <returns></returns>
        //public static DataTable SelectDetailData(string strBillID)
        //{
        //    return SelectHeadData(strBillID);
        //}
        #endregion



        #region AUD
        public static bool SaveOrUpdate(BsOutrepairEntity entity, bool isNew)
        {
            bool istag = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                broker.BeginTransaction();
                try
                {
                    if (isNew)
                    {
                        DataAccessCommon.Insert(entity, broker);
                    }
                    else
                    {
                        DataAccessCommon.UpdateOnly(entity, broker);
                    }

                    broker.Commit();
                }
                catch
                {
                    istag = false;
                    broker.Rollback();
                    throw;
                }
                return istag;
            }
        }

        public static bool Del(string strCode)
        {
            bool isResult = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    DataAccessCommon.Delete(strCode, "BillID", "BS_Outrepair");
                    broker.Commit();
                }
                catch (Exception ex)
                {
                    isResult = false;
                    broker.Rollback();
                    throw ex;
                }
            }
            return isResult;
        }
        #endregion
    }
}
